package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.edu.bean.News;
import com.edu.bean.Upkeep;
import com.edu.dao.BaseDao;
import com.edu.dao.UpkeepDao;

public class UpkeepDaoImpl implements UpkeepDao {

	@Override
	public List<Upkeep> selectUpkeepAll(int pageNum, int pageSize) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Upkeep> upkeep = new ArrayList();
		String sql = "SELECT * FROM upkeep LIMIT ?,?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNum-1)*pageSize);
			ps.setInt(2, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				Upkeep up = new Upkeep();
				up.setUpkeepsId(res.getInt("upkeepsId"));
				up.setCar_id(res.getInt("car_id"));
				up.setUser_id(res.getInt("user_id"));
				up.setUpkeepname(res.getString("upkeepname"));
				up.setPrice(res.getDouble("price"));
				up.setIntroduce(res.getString("introduce"));
				up.setState(res.getString("state"));
				up.setCreated_at(res.getDate("created_at"));
				up.setUpdated_at(res.getDate("updated_at"));
				upkeep.add(up);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return upkeep;
	}

	@Override
	public int selectupkeeptotal(Upkeep upkeep) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM upkeep WHERE 1=1 ";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int deleteupkeepById(int upkeepid) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM upkeep WHERE upkeepsId = ?";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, upkeepid);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		
		return result;
	}

	@Override
	public int updateUpkeep(Upkeep upkeep) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE upkeep set user_id = ?,car_id = ?,upkeepName = ?,price = ?,introduce= ?,state= ?,created_at= ?,updated_at= ? WHERE upkeepsId = ? ";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, upkeep.getUser_id());
			ps.setInt(2, upkeep.getCar_id());
			ps.setString(3, upkeep.getUpkeepname());
			ps.setDouble(4, upkeep.getPrice());
			ps.setString(5, upkeep.getIntroduce());
			ps.setString(6, upkeep.getState());
			ps.setDate(7, new Date(upkeep.getCreated_at().getTime()));
			ps.setDate(8, new Date(upkeep.getUpdated_at().getTime()));
			ps.setInt(9, upkeep.getUpkeepsId());
			result = ps.executeUpdate();
			System.out.println(ps);
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public Upkeep selectUpkeepById(int upkeepid) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Upkeep upkeep=null;
		String sql = "SELECT * FROM upkeep WHERE upkeepsId = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,upkeepid);
			res = ps.executeQuery();
			if(res.next()) {
				upkeep = new Upkeep();
				upkeep.setUpkeepsId(res.getInt("upkeepsId"));
				upkeep.setCar_id(res.getInt("car_id"));
				upkeep.setUser_id(res.getInt("user_id"));
				upkeep.setUpkeepname(res.getString("upkeepname"));
				upkeep.setPrice(res.getDouble("price"));
				upkeep.setIntroduce(res.getString("introduce"));
				upkeep.setState(res.getString("state"));
				upkeep.setCreated_at(res.getDate("created_at"));
				upkeep.setUpdated_at(res.getDate("updated_at"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return upkeep;
	}

	@Override
	public int addupkeep(Upkeep upkeep) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "INSERT INTO upkeep(user_id,car_id,upkeepname,price,introduce,state,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?)";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, upkeep.getUser_id());
			ps.setInt(2, upkeep.getCar_id());
			ps.setString(3, upkeep.getUpkeepname());
			ps.setDouble(4, upkeep.getPrice());
			ps.setString(5, upkeep.getIntroduce());
			ps.setString(6, upkeep.getState());
			ps.setDate(7, new Date(upkeep.getCreated_at().getTime()));
			ps.setDate(8, new Date(upkeep.getUpdated_at().getTime()));
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

}
